﻿using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace EdalSoft.Data
{
    public class MySql
    {
        private MySqlConnection con;
        private MySqlTransaction tran;
        private bool istransactional;

        /// <summary>
        /// Test Database Connection
        /// </summary>
        /// <param name="conString">Specify the connection string for the connection</param>
        /// <returns></returns>
        public static bool TestConnection(string conString)
        {
            var conn = new MySqlConnection(conString);
            try
            {
                conn.Open();
                conn.Close();
                conn.Dispose();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Creates a new DataLayer instance with the specified Connection String
        /// </summary>
        /// <param name="conString">Specify the connection string for the connection</param>
        /// <param name="IsTransactional">Specify if the connection will be transactional</param>
        public MySql(string conString, bool isTransactional = false)
        {
            con = new MySqlConnection(conString);
            if (isTransactional)
            {
                con.Open();
                tran = con.BeginTransaction();
            }
            istransactional = isTransactional;
        }

        /// <summary>
        /// Execute an Stored procedure that doesn't return values
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        public void ExecuteProcedure(string storedProcedure, Parameters parameters = null)
        {
            using (var cmd = new MySqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("_" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (!istransactional)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Execute an Stored Procedure that has Output Parameters
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public Parameters GetOutputParameters(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new MySqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                    {
                        var par = new MySqlParameter();
                        par.Direction = sqlpar.Direction == Parameters.Parameter.ParDirection.Out ? ParameterDirection.Output : ParameterDirection.Input;
                        par.ParameterName = "_" + sqlpar.Name;
                        par.Value = sqlpar.Value;
                        par.Size = sqlpar.Size;
                        cmd.Parameters.Add(par);
                    }
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (!istransactional)
                    cmd.Connection.Close();

                Parameters outpars = new Parameters();
                foreach (MySqlParameter par in cmd.Parameters)
                    if (par.Direction == ParameterDirection.Output)
                        outpars.Add(par.ParameterName, par.Value, Parameters.Parameter.ParDirection.Out);
                return outpars;
            }
        }

        /// <summary>
        /// Execute Stored Procedure and return the Scalar
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object GetScalar(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new MySqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("_" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                object res = cmd.ExecuteScalar();
                if (!istransactional)
                    cmd.Connection.Close();
                return res;
            }
        }

        /// <summary>
        /// Execute Stored Procedure and return a DataTable
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string storedProcedure, Parameters parameters = null)
        {
            using (MySqlCommand cmd = new MySqlCommand(storedProcedure, con)
                                          {
                                              CommandType = CommandType.StoredProcedure
                                          })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("_" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                if (!istransactional)
                    cmd.Connection.Close();
                return dt;
            }
        }

        public MySqlDataReader ExecuteReader(string storedProcedure, Parameters parameters = null)
        {
            using (MySqlCommand cmd = new MySqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                {
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                    { cmd.Parameters.AddWithValue("_" + sqlpar.Name, sqlpar.Value); }
                }
                if (istransactional)
                { cmd.Transaction = tran; }
                if (cmd.Connection.State == ConnectionState.Closed)
                { cmd.Connection.Open(); }
                return cmd.ExecuteReader();
            }
        }

        /// <summary>
        /// Execute an specified Query that doesn't return values
        /// </summary>
        /// <param name="Query"></param>
        /// <param name="parameters"></param>
        public void ExecuteQuery(string Query, Parameters parameters=null)
        {
            using (MySqlCommand cmd = new MySqlCommand(Query, con) { CommandType = CommandType.Text })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("_" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                if (!istransactional)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Commit Actual Transaction
        /// </summary>
        public void CommitTransaction()
        {
            tran.Commit();
            con.Close();
        }

        /// <summary>
        /// Rollback Actual Transaction
        /// </summary>
        public void RollbackTransaction()
        {
            tran.Rollback();
            con.Close();
        }

        /// <summary>
        /// Close Active Connection
        /// </summary>
        public void CloseConnection()
        {
            if (con.State != ConnectionState.Closed)
            { con.Close(); }
        }

        /// <summary>
        /// Open Actual Connection
        /// </summary>
        public void OpenConnection()
        {
            if (con.State != ConnectionState.Open)
            { con.Open(); }
        }
    }
}